<?php

namespace Muyuxuan\excel;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xls;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class Excel
{
    protected static $wen = ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"];
    //导入
    public static function import(string $fileName=''){
        $extension = strtolower( pathinfo($fileName, PATHINFO_EXTENSION) );
        if ($extension =='xlsx') {
            $objReader = IOFactory::createReader('Xlsx');
            $objPHPExcel = $objReader ->load($fileName);
        } else if ($extension =='xls') {
            $objReader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
            $objPHPExcel = $objReader ->load($fileName);
        } else if ($extension=='csv') {
            $PHPReader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
            $PHPReader->setInputEncoding('UTF-8');    //默认输入字符集
            $PHPReader->setDelimiter(',');      //默认的分隔符
            $objPHPExcel = $PHPReader->load($fileName);     //载入文件
        }
        $sheetData = $objPHPExcel->getActiveSheet()->ToArray();   // 转成数组
        return $sheetData;
    }

    /**导出
     * @param array $headers [['field'=>"name",'text'=>"名字","width"=>20,"setARGB"=>"FFFF0000"]]
     * @param array $data 二维数组数据
     * @param string $fileName 文件名称
     * @param string $type 类型:xlsx/xls
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public static function export(array $headers,array $data=[],$fileName="",string $type='xlsx'){
        if(empty($fileName)){
            $fileName = './'.time().'.'.$type;
        }
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        foreach ($headers as $key=>$value){
            $sheet->setCellValue(self::getWen($key).'1',$value['text']);
            if(!empty($value['width'])){
                $sheet->getColumnDimension(self::getWen($key))->setWidth($value['width']);
            }else{
                $sheet->getColumnDimension(self::getWen($key))->setAutoSize(true);
            }
            if(!empty($value['setARGB'])){
                $sheet->getStyle(self::getWen($key).'1')->getFont()->getColor()->setARGB($value['setARGB']);
            }
            foreach ($data as $k=>$v){
                if(isset($v[$value['field']])){
//                    echo $this->getWen($key).($k+2).'|';
                    $sheet->setCellValue(self::getWen($key).($k+2),$v[$value['field']]."\t");
                    if(!empty($value['setARGB'])){
                        $sheet->getStyle(self::getWen($key).($k+2))->getFont()->getColor()->setARGB($value['setARGB']);
                    }
                }
            }
        }
        if(strtolower($type)=='xlsx'){
            $writer = new Xlsx($spreadsheet);
        }elseif (strtolower($type)=='xls'){
            $writer = new Xls($spreadsheet);
        }
        $writer->save($fileName);
        return true;
    }
    protected static function getWen($num){

        if($num>25){
            $num = $num%26;
            $int = intval($num/26);
            $str = '';
            for ($a=0;$a<$int;$a++){
                $str .= self::$wen[$int];
            }
            $str .= self::$wen[$num];
        }else{
            $str = self::$wen[$num];
        }
        return $str;
    }

}